The main components of storytelling with data are:
- Understanding and setting the context
- Exploring multiple angles - main data + supplemental data
- Using compelling visualizations
- Using varied data sources
- Having a consistent narrative
Background Information¶
Before diving into analyzing the data, it’s useful to research some background information. In this case, we know a few facts that will be useful:
- New York City is divided into 5 boroughs, which are essentially distinct regions.
- Schools in New York City are divided into several school district, each of which can contains dozens of schools.
- Not all the schools in all of the datasets are high schools, so we’ll need to do some data cleaning.
- Each school in New York City has a unique code called a DBN, or District Borough Number.
- By aggregating data by district, we can use the district mapping data to plot district-by-district differences.
Understanding the Data¶
- Loop through each data file we downloaded.
- Read the file into a Pandas DataFrame.
- Put each DataFrame into a Python dictionary.
In [1]:
import pandas
import numpy as np
files = ["ap_results.csv", "class_size.csv", "demographics.csv", "graduation.csv", "hs_directory.csv", "math_test_results.csv", "sat_results.csv"]
data = {}
for f in files:
d = pandas.read_csv("./data/NYC-Schools/{0}".format(f),encoding="ISO-8859-1")
data[f.replace(".csv", "")] = d
In [2]:
#data["hs_directory"].head()
for k,v in data.items():
print("\n" + k + "\n")
print(v.head())
Initial thoughts:
- Most of the datasets contain a DBN column - use to join the data sets. Will need to add this column in class_size dataset.
- Some fields look interesting for mapping, particularly Location 1, which contains coordinates inside a larger string.
- Some of the datasets appear to contain multiple rows for each school (repeated DBN values), which means we’ll have to do some preprocessing.
DBM¶
All of the datasets have a DBN (unique code for each school) except for the class_size dataset. We will need to see if we can create this column there.
In [3]:
# what our DBM should look like
data["demographics"]["DBN"].head()
Out[3]:
In [4]:
data["class_size"].head()
Out[4]:
In [5]:
# constructing the DBM for class_size
data["class_size"]["DBN"] = data["class_size"].apply(lambda x: "{0:02d}{1}".format(x["CSD"], x["SCHOOL CODE"]), axis=1)
In [6]:
data["class_size"]["DBN"].head()
Out[6]:
Survey Data¶
- Read in the surveys for all schools using the windows-1252 file encoding.
- Read in the surveys for district 75 schools using the windows-1252 file encoding.
- Add a flag that indicates which school district each dataset is for.
- Combine the datasets into one using the concat method on DataFrames.
In [7]:
survey1 = pandas.read_csv("./data/NYC-Schools/survey_all.txt", delimiter="\t", encoding='windows-1252')
survey2 = pandas.read_csv("./data/NYC-Schools/survey_d75.txt", delimiter="\t", encoding='windows-1252')
survey1["d75"] = False
survey2["d75"] = True
survey = pandas.concat([survey1, survey2], axis=0)
In [8]:
survey.head()
Out[8]:
In [9]:
# only keep useful columns
survey["DBN"] = survey["dbn"]
survey_fields = ["DBN", "rr_s", "rr_t", "rr_p", "N_s", "N_t", "N_p", "saf_p_11", "com_p_11", "eng_p_11", "aca_p_11", "saf_t_11", "com_t_11", "eng_t_10", "aca_t_11", "saf_s_11", "com_s_11", "eng_s_11", "aca_s_11", "saf_tot_11", "com_tot_11", "eng_tot_11", "aca_tot_11",]
survey = survey.loc[:,survey_fields]
data["survey"] = survey
survey.shape
Out[9]:
Condensing Rows¶
In [10]:
data["class_size"].head()
Out[10]:
The class_size dataset has multiple rows per school - for different grades and program types. We'll need to condense these in order to join them with the other datasets. To filter out the duplicates, we can:
- Only select values from class_size where the GRADE field is 09-12.
- Only select values from class_size where the PROGRAM TYPE field is GEN ED.
- Group the class_size dataset by DBN, and take the average of each column. Essentially, we’ll find the average class_size values for each school.
- Reset the index, so DBN is added back in as a column.
In [11]:
class_size = data["class_size"]
class_size = class_size[(class_size["GRADE "] == "09-12") & \
(class_size["PROGRAM TYPE"] == "GEN ED")]
class_size = class_size.groupby("DBN").agg(np.mean)
class_size.reset_index(inplace=True)
data["class_size"] = class_size
In [12]:
data["class_size"].head()
Out[12]:
The demographics dataset also contains duplicates for different school years. We will filter to show onlythe 2011-2012 year.
In [13]:
demographics = data["demographics"]
demographics = demographics[demographics["schoolyear"] == 20112012]
data["demographics"] = demographics
In [14]:
data["demographics"].head()
Out[14]:
The math_test_results dataset contains multiple rows per school for different years and grades. We will filter to show 2011 scores for grade 8 only.
In [15]:
data["math_test_results"] = data["math_test_results"][data["math_test_results"]["Year"] == 2011]
data["math_test_results"] = data["math_test_results"][data["math_test_results"]["Grade"] == '8']
In [16]:
data["math_test_results"].head()
Out[16]:
The graduation dataset contains multiple rows per school for different cohorts and demographics. We will filter for 2006 and Total Cohort only.
In [17]:
data["graduation"] = data["graduation"][data["graduation"]["Cohort"] == "2006"]
data["graduation"] = data["graduation"][data["graduation"]["Demographic"] == "Total Cohort"]
In [18]:
data["graduation"].head()
Out[18]:
Computing Variables¶
We can compute a total SAT score from the individual columns SAT Math Avg. Score, SAT Critical Reading Avg. Score, and SAT Writing Avg. Score. In the below code, we:
- Convert each of the SAT score columns from a string to a number.
- Add together all of the columns to get the sat_score column, which is the total SAT score.
In [19]:
data["sat_results"].head()
Out[19]:
In [20]:
# we want to convert the scores from string objects to numbers
data["sat_results"].info()
In [21]:
cols = ['SAT Math Avg. Score', 'SAT Critical Reading Avg. Score', 'SAT Writing Avg. Score']
for c in cols:
data["sat_results"][c] = pandas.to_numeric(data["sat_results"][c])
data['sat_results']['sat_score'] = data['sat_results'][cols[0]] + data['sat_results'][cols[1]] + data['sat_results'][cols[2]]
In [22]:
data["sat_results"].info()
We can parse out the coordinate locations of each school to plot the schools on a map.
- Parse latitude and longitude columns from the Location 1 column.
- Convert lat and lon to be numeric.
In [23]:
data["hs_directory"]['lat'] = data["hs_directory"]['Location 1'].apply(lambda x: x.split("\n")[-1].replace("(", "").replace(")", "").split(", ")[0])
data["hs_directory"]['lon'] = data["hs_directory"]['Location 1'].apply(lambda x: x.split("\n")[-1].replace("(", "").replace(")", "").split(", ")[1])
for c in ['lat', 'lon']:
data["hs_directory"][c] = pandas.to_numeric(data["hs_directory"][c])
In [24]:
data["hs_directory"].head()
Out[24]:
Combining Datasets¶
http://pandas.pydata.org/pandas-docs/stable/merging.html
- Loop through each of the items in the data dictionary.
- Print the number of non-unique DBNs in the item.
- Decide on a join strategy – inner or outer.
- Join the item to the DataFrame full using the column DBN.
In [25]:
flat_data_names = [k for k,v in data.items()]
flat_data = [data[k] for k in flat_data_names]
full = flat_data[0]
for i, f in enumerate(flat_data[1:]):
name = flat_data_names[i+1]
print(name)
print(len(f["DBN"]) - len(f["DBN"].unique()))
join_type = "inner"
if name in ["sat_results", "ap_2010", "graduation"]:
join_type = "outer"
if name not in ["math_test_results"]:
full = full.merge(f, on="DBN", how=join_type)
full.shape
Out[25]:
Additional Data Cleaning¶
Notice that some rows in the ap_2010 file contain 's' which we will need to change.
In [26]:
data["ap_results"].head()
Out[26]:
In [27]:
# transform AP columns to numbers and fill in nulls
cols = ['Num of AP Test Takers', 'Num of AP Total Exams Taken', 'Num of AP Exams Passed']
for col in cols:
full[col] = pandas.to_numeric(full[col])
full[cols] = full[cols].fillna(value=0)
Add in column for school district based first 2 digits on DBN.
In [28]:
full["school_dist"] = full["DBN"].apply(lambda x: x[:2])
Fill in any missing values in full with the mean of the column, so we can compute correlations.
In [29]:
full = full.fillna(full.mean())
Computing Correlations¶
In [30]:
full.corr()['sat_score']
Out[30]:
This gives us quite a few insights that we’ll need to explore:
- Total enrollment correlates strongly with sat_score, which is surprising, because you’d think smaller schools, which focused more on the student, would have higher scores.
- The percentage of females at a school (female_per) correlates positively with SAT score, whereas the percentage of males (male_per) correlates negatively.
- None of the survey responses correlate highly with SAT scores.
- There is a significant racial inequality in SAT scores (white_per, asian_per, black_per, hispanic_per).
- ell_percent correlates strongly negatively with SAT scores.
In [31]:
full.head()
Out[31]:
Mapping¶
In [32]:
import folium
from folium import plugins
schools_map = folium.Map(location=[full['lat'].mean(), full['lon'].mean()], zoom_start=10)
marker_cluster = folium.MarkerCluster().add_to(schools_map)
for name, row in full.iterrows():
folium.Marker([row["lat"], row["lon"]], popup="{0}: {1}".format(row["DBN"], row["SCHOOL NAME_x"])).add_to(marker_cluster)
schools_map
Out[32]:
A Heatmap might make it easier to see the schools.
In [33]:
schools_heatmap = folium.Map(location=[full['lat'].mean(), full['lon'].mean()], zoom_start=10)
schools_heatmap.add_child(plugins.HeatMap([[row["lat"], row["lon"]] for name, row in full.iterrows()]))
schools_heatmap
Out[33]:
We can compute SAT score by school district, then plot this out on a map.
- Group 'full' by school district.
- Compute the average of each column for each school district. This will give us 1 row per school district (currently 32 districts).
- Convert the school_dist field to remove leading 0s, so we can match our geographic district data.
In [34]:
district_data = full.groupby("school_dist").agg(np.mean)
district_data.reset_index(inplace=True)
district_data["school_dist"] = district_data["school_dist"].apply(lambda x: str(int(x)))
In [35]:
district_data.head()
Out[35]:
In [36]:
geo_path = './data/NYC-Schools/districts.geojson'
districts = folium.Map(location=[full['lat'].mean(), full['lon'].mean()], zoom_start=10)
districts.choropleth(geo_path=geo_path, data=district_data,
columns=['school_dist', 'sat_score'],
key_on='feature.properties.school_dist',
fill_color='YlGn', fill_opacity=0.7, line_opacity=0.7,
legend_name='SAT Score')
# add markers
# for name, row in district_data.iterrows():
# folium.CircleMarker([row["lat"], row["lon"]], radius=10, popup="{0}: {1}".format(row["school_dist"], row["sat_score"])).add_to(districts)
#folium.LatLngPopup().add_to(districts)
districts.save('districts.html')
districts
Out[36]:
Exploring Enrollment and SAT Scores¶
In [37]:
%matplotlib inline
full.plot.scatter(x='total_enrollment', y='sat_score')
Out[37]:
In [50]:
full[["DBN","SCHOOL NAME_x","total_enrollment"]]
full[pandas.isnull(full["School Name"])][["SCHOOL NAME_x", "DBN", "School Name"]]
Out[50]:
In [51]:
full.plot.scatter(x='ell_percent', y='sat_score')
Out[51]:
In [ ]: